package Network

import (
	"SQL/myDB"
	"database/sql"
	"errors"
	"fmt"
	"logs"
	"net/http"
	"os"
	"time"
)

func setData(w http.ResponseWriter, req *http.Request) {
	printRequestInfo(req)
	w.Header().Set("Content-Type", "text/html;charset=utf-8")
	w.Write([]byte(`
<!DOCTYPE html>
<html>
<head>
<title>设置数据成功</title>
</head>
<body>
	`))
	defer w.Write([]byte(`
<body>
</html>
	`))

	err := req.ParseForm()
	if err != nil {
		logs.Print("setData error:", err.Error())
		w.Write([]byte(err.Error()))
		return
	}

	page := req.FormValue("page")
	err = resetDB(page)
	if err != nil {
		logs.Print("setData error:", err.Error())
		w.Write([]byte(err.Error()))
		return
	}

	w.Write([]byte(`
	<script>window.location.href="/dbPage"</script> 
	<a href=/dbPage>若没有跳转请点这里</a>
	`))
}

func resetDB(page string) (err error) {
	db := openDB()
	defer closeDB(db)

	before := time.Now()
	switch page {
	case "resetDB":
		err = createTables(db)
	case "copyDB":
		err = copyDB(db)
	default:
		err = errors.New("page not exists")
	}
	logs.Print(page, "use", time.Now().Sub(before))
	return err
}

func createTables(db *sql.DB) error {
	myDB.ExecSql("truncate objects cascade;")
	Sql := []string{
		`
--删表:
drop table if exists sessions;
drop table if exists messages;
drop table if exists members;
drop table if exists groups;
drop table if exists users;
drop table if exists objects;

create table if not exists objects (
	id serial8 primary key,
	nickname varchar(200) not null,			--nickname修改为not null
	iconURL varchar(500) default '/icon/default.ico' not null,			
	isUser bool not null,
	custom jsonb default '{}'::jsonb not null
);
--例子：
select setval('objects_id_seq',99999999);--设置起始值
insert into objects(nickname,isUser) values('老猫',true);
insert into objects(nickname,isUser) values('dy',true);
insert into objects(nickname,isUser) values('Ficow',true);
insert into objects(nickname,isUser) values('Decaf',true);
insert into objects(nickname,isUser) values('Pismery',true);
insert into objects(nickname,isUser) values('嵌入式讨论群',false);
insert into objects(nickname,isUser) values('吹水群6',false);
insert into objects(nickname,isUser) values('kemin',true);
insert into objects(nickname,isUser) values('jimmy',true);
--2.users(id int64 fk pk,account string unique,password string,lastOnlineTime time)
create table if not exists users (
	id int8 primary key,
	account varchar(200) not null,
	password varchar(200) not null,
	lastOnlineTime timestamp with time zone default now() not null
);
alter table users drop constraint if exists fk_id;
alter table users drop constraint if exists unique_account;
alter table users add constraint fk_id foreign key (id) references objects(id) on delete cascade;
alter table users add constraint unique_account unique(account);

--例子：
insert into users(id,account,password) values(100000000,'13580452503','123456');
insert into users(id,account,password) values(100000001,'15602309617','123456');
insert into users(id,account,password) values(100000002,'15521133823','123456');
insert into users(id,account,password) values(100000003,'18826054758','123456');
insert into users(id,account,password) values(100000004,'15626030466','123456');
insert into users(id,account,password) values(100000007,'15602303588','123456');
insert into users(id,account,password) values(100000008,'18675502863','123456');
--3.groups(id int64 fk pk,owner int64 fk,createtime time)
create table if not exists groups (
	id int8 primary key,
	owner int8 not null,
	createTime timestamp with time zone default now() not null
);
alter table groups drop constraint if exists fk_id;
alter table groups drop constraint if exists fk_owner;
alter table groups add constraint fk_id foreign key (id) references objects(id) on delete cascade;
alter table groups add constraint fk_owner foreign key (owner) references objects(id) on delete cascade;

--例子：
insert into groups(id,owner) values(100000005,100000000);
insert into groups(id,owner) values(100000006,100000000);
--4.members(groupID int64 fk,userID int64 fk,pk(groupID,userID))

create table if not exists members (
	groupID int8 not null,
	userID int8 not null,
	isManager bool not null,
	primary key (groupID,userID)
);
alter table members drop constraint if exists fk_groupID;
alter table members drop constraint if exists fk_userID;
alter table members add constraint fk_groupID foreign key (groupID) references objects(id) on delete cascade;
alter table members add constraint fk_userID foreign key (userID) references objects(id) on delete cascade;

--例子：
insert into members(groupID,userID,isManager) values 
(100000005,100000000,true),
(100000005,100000001,false),
(100000005,100000002,false),
(100000005,100000003,false),
(100000005,100000004,false),
(100000005,100000007,false),
(100000005,100000008,false);
insert into members(groupID,userID,isManager) values 
(100000006,100000000,true),
(100000006,100000001,false),
(100000006,100000002,true),
(100000006,100000003,false);
DROP TYPE IF EXISTS MsgType;
CREATE TYPE MsgType AS ENUM ('text', 'image','audio', 'file');
--5.messages(id int64 pk,content string,sender int64 fk,receiver int64 fk,createTime time)

create table if not exists messages (
	id serial8 primary key,
	content varchar(2000) not null,
	sender int8 not null,
	receiver int8 not null,
	type MsgType not null,
	createTime timestamp with time zone default now() not null
);
alter table messages drop constraint if exists fk_sender;
alter table messages drop constraint if exists fk_receiver;
alter table messages add constraint fk_sender foreign key (sender) references objects(id) on delete cascade;
alter table messages add constraint fk_receiver foreign key (receiver) references objects(id) on delete cascade;

--例子：
insert into messages(content,sender,receiver,type) values('10001:你好 user2!我要给你发一条很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！',100000001,100000002,'text');
insert into messages(content,sender,receiver,type) values('10002:嘿 user1!在吗？',100000002,100000001,'text');
insert into messages(content,sender,receiver,type) values('10002:嘿 user4!呃，没事。。。',100000002,100000004,'text');
insert into messages(content,sender,receiver,type) values('10003:你好 user1! user1。。。。。。。',100000003,100000001,'text');
insert into messages(content,sender,receiver,type) values('10001:嘿 group5!我要给你发一条很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！正常吗？',100000001,100000005,'text');
insert into messages(content,sender,receiver,type) values('10001:嘿 group6!呃，我来测试一下你的消息气泡的高度是不是有问题！',100000001,100000006,'text');
insert into messages(content,sender,receiver,type) values('10002:嗨 group5!我要给你发一条很长很长很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！正常吗？要不然，我再说点啥？你再看下，现在看下正常不？？？？？？？',100000002,100000005,'text');
insert into messages(content,sender,receiver,type) values('10003:嗨 group6! 1.0版本啥时候诞生呢？',100000003,100000006,'text');
insert into messages(content,sender,receiver,type) values('10004:你好 group6!我也要给你发一条，一条很长很长的消息，然后让你可以测试一下你的消息气泡的高度是不是有问题！正常了吧？',100000004,100000006,'text');
--6.sessions(id serial8,senderID int64 fk,receiverID  int64 fk,lastMsgID int64 fk,pk(id,senderID,receiverID))；
create table if not exists sessions (
	id serial8 not null,
	senderID int8 not null,
	receiverID int8 not null,
	lastMsgID int8 not null,
	primary key (id,senderID,receiverID)
);
alter table sessions drop constraint if exists fk_senderID;
alter table sessions drop constraint if exists fk_receiverID;
alter table sessions drop constraint if exists fk_lastMsgID;
alter table sessions add constraint fk_senderID foreign key (senderID)  references objects(id) on delete cascade;
alter table sessions add constraint fk_receiverID foreign key (receiverID)  references objects(id) on delete cascade;
alter table sessions add constraint fk_lastMsgID foreign key (lastMsgID)  references messages(id) on delete cascade;


--例子：
insert into sessions(senderID,receiverID,lastMsgID) values(100000002,100000001,2);
insert into sessions(senderID,receiverID,lastMsgID) values(100000002,100000004,3);
insert into sessions(senderID,receiverID,lastMsgID) values(100000003,100000001,4);
insert into sessions(senderID,receiverID,lastMsgID) values(100000002,100000005,7);
insert into sessions(senderID,receiverID,lastMsgID) values(100000004,100000006,9);
--index
drop index if exists objects_id_index;
drop index if exists users_account_index;
drop index if exists users_id_index;
drop index if exists groups_id_index;
drop index if exists members_index;
drop index if exists messages_index;
drop index if exists sessions_index;
create index if not exists objects_id_index on objects (id);
create index if not exists users_account_index on users(account);
create index if not exists users_id_index on users(id);
create index if not exists groups_id_index on groups (id);
create index if not exists members_index on members(groupID,userID);
create index if not exists messages_index on messages(sender,receiver);
create index if not exists sessions_index on sessions (id,senderID,receiverID);
`,
	}
	return execSql(Sql, db)
}

func copyDB(db *sql.DB) error {
	objectsFileName := "/tmp/objects.txt"
	groupsFileName := "/tmp/groups.txt"
	usersFileName := "/tmp/users.txt"
	defer func() {
		os.Remove(objectsFileName)
		os.Remove(groupsFileName)
		os.Remove(usersFileName)
	}()
	before := time.Now()
	var begID int
	rows, err := myDB.QuerySql("select nextval('objects_id_seq');")
	if err != nil {
		return err
	}
	if !rows.Next() {
		return errors.New("rows has no next")
	}
	err = rows.Scan(&begID)
	if err != nil {
		return err
	}
	n := config.CopyDataNum

	//objects data
	objectsFile, err := os.OpenFile(objectsFileName, os.O_CREATE|os.O_TRUNC|os.O_WRONLY, 0666)
	if err != nil {
		return err
	}
	for i := 0; i < n; i++ { //users
		_, err := objectsFile.WriteString(fmt.Sprintf("%v\t%v\t%v\n", i+begID, i+begID, true))
		if err != nil {
			return err
		}
	}
	for i := 0; i < n; i++ { //groups
		_, err := objectsFile.WriteString(fmt.Sprintf("%v\t%v\t%v\n", i+n+begID, i+n+begID, false))
		if err != nil {
			return err
		}
	}
	err = objectsFile.Close()
	if err != nil {
		return err
	}

	//groups data
	groupsFile, err := os.OpenFile(groupsFileName, os.O_CREATE|os.O_TRUNC|os.O_WRONLY, 0666)
	if err != nil {
		return err
	}
	for i := 0; i < n; i++ {
		_, err := groupsFile.WriteString(fmt.Sprintf("%v\t%v\n", i+begID+n, begID+i))
		if err != nil {
			return err
		}
	}
	err = groupsFile.Close()
	if err != nil {
		return err
	}

	//users data
	usersFile, err := os.OpenFile(usersFileName, os.O_CREATE|os.O_TRUNC|os.O_WRONLY, 0666)
	if err != nil {
		return err
	}
	const phoneNum = (int64)(1e10)
	for i := 0; i < n; i++ {
		_, err = usersFile.WriteString(fmt.Sprintf("%v\t%v\t123456\n", i+begID, (int64)(i+begID)+phoneNum))
		if err != nil {
			return err
		}
	}
	err = usersFile.Close()
	if err != nil {
		return err
	}
	logs.Print("generate date use", time.Now().Sub(before))

	Sql := []string{
		`alter table users drop constraint if exists unique_account;
	alter table users drop constraint if exists fk_id;
	alter table groups drop constraint if exists fk_id;
	alter table groups drop constraint if exists fk_owner;
	alter table members drop constraint if exists fk_groupID;
	alter table members drop constraint if exists fk_userID;
	alter table messages drop constraint if exists fk_sender;
	alter table messages drop constraint if exists fk_receiver;
	alter table sessions drop constraint if exists fk_senderID;
	alter table sessions drop constraint if exists fk_receiverID;
	alter table sessions drop constraint if exists fk_lastMsgID;
	drop index if exists objects_id_index;
	drop index if exists users_account_index;
	drop index if exists users_id_index;
	drop index if exists groups_id_index;
	drop index if exists members_index;
	drop index if exists messages_index;
	drop index if exists sessions_index;
`,
		"copy objects(id,nickname,isUser) from '" + objectsFileName + "'",
		"copy groups(id,owner) from '" + groupsFileName + "'",
		"copy users(id,account,password) from '" + usersFileName + "'",
		fmt.Sprintf("select setval('objects_id_seq',%v);--设置起始值", begID+n+n),
		`alter table users drop constraint if exists unique_account;
	alter table users drop constraint if exists fk_id;
	alter table groups drop constraint if exists fk_id;
	alter table groups drop constraint if exists fk_owner;
	alter table members drop constraint if exists fk_groupID;
	alter table members drop constraint if exists fk_userID;
	alter table messages drop constraint if exists fk_sender;
	alter table messages drop constraint if exists fk_receiver;
	alter table sessions drop constraint if exists fk_senderID;
	alter table sessions drop constraint if exists fk_receiverID;
	alter table sessions drop constraint if exists fk_lastMsgID;
	create index if not exists objects_id_index on objects (id);
	create index if not exists users_account_index on users(account);
	create index if not exists users_id_index on users(id);
	create index if not exists groups_id_index on groups (id);
	create index if not exists members_index on members(groupID,userID);
	create index if not exists messages_index on messages(sender,receiver);
	create index if not exists sessions_index on sessions (id,senderID,receiverID);
`,
	}
	return execSql(Sql, db)
	return nil
}

func execSql(Sql []string, db *sql.DB) (err error) {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()
	for _, val := range Sql {
		_, err := tx.Exec(val)
		if err != nil {
			return err
		}
	}
	return nil
}
